package com.hdb;

import com.hdb.dao.DataFilter;
import com.hdb.dao.Hib;
import com.hdb.dao.Types;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import java.sql.Timestamp;
import java.util.List;

/**
 * Created by aszer on 2016/11/10.
 */
public class DB {
    public Hib<TAdmin> adminDAO;
    public Hib<TGood> goodDAO;
    public Hib<TMyOrder> orderDAO;
    public Hib<TOrderGood> orderGoodDAO;
    public Hib<TClassification> classDAO;
    public Hib<TNotice> noticeDAO;
    public Hib<TUser> userDAO;
    public Hib<TComment> commentDAO;
    public Hib<TComment> tableInfoDAO;
    private final Log log = LogFactory.getLog(Hib.class);

    public Session getSession() {
        return Hib.getSession();
    }

    /*通过管理员账户和密码查找管理员*/
    public TAdmin findAdmin(String adminName, String adminPw) {
        String HQL = "from TAdmin where adminName=:adminName and adminPw=:adminPw";
        Session session = getSession();
        List adminList = session.createQuery(HQL)
                .setParameter("adminName", adminName)
                .setParameter("adminPw", adminPw)
                .setMaxResults(1)
                .list();
        session.close();
        return adminList.isEmpty() ? null : (TAdmin) adminList.get(0);
    }

    /*查找所有包含制定商品的订单*/
    public List<TMyOrder> findOrderHaveGood(int goodId, int start, int length) {
        try {
            String queryString = "select TMyOrder from TMyOrder as model1,TOrderGood as model2 where model1.orderId= model2.orderId and model2.goodId=:goodId and model1.orderState=0";
            List res = getSession()
                    .createQuery(queryString)
                    .setParameter("goodId", goodId)
                    .setFirstResult(start)
                    .setMaxResults(length)
                    .list();
            return DataFilter.filterT(res, TMyOrder.class);
        } catch (RuntimeException re) {
            log.error("未找到键", re);
        }
        return null;
    }

    /*通过用户ID查找属于用户的订单*/
    public List<TMyOrder> findOrder(int userId, int start, int length) {
        try {
            String queryString = "from TMyOrder as model where model.userId= ? order by orderDate desc ";
            return DataFilter.filterT(getSession()
                    .createQuery(queryString)
                    .setParameter(0, userId)
                    .setFirstResult(start)
                    .setMaxResults(length)
                    .list(), TMyOrder.class);
        } catch (RuntimeException re) {
            log.error("未找到键", re);
        }
        return null;
    }

    /*通过用户名密码查找用户*/
    public TUser findUser(String userName, String userPw) {
        String HQL = "from TUser where userName=:userName and userPw=:userPw";
        Session session = getSession();
        List userList = session.createQuery(HQL)
                .setParameter("userName", userName)
                .setParameter("userPw", userPw)
                .setMaxResults(1)
                .list();
        session.close();
        return userList.isEmpty() ? null : (TUser) userList.get(0);
    }

    /*通过类别ID查找商品*/
    public List<TGood> goodByClass(int classId, int start, int length) {
        String HQL = "from TGood where goodState=:goodState and classId=:classId";
        Session session = getSession();
        List res = session.createQuery(HQL)
                .setParameter("goodState", Types.good.OnShelves)
                .setParameter("classId", classId)
                .setFirstResult(start)
                .setMaxResults(length)
                .list();
        session.close();
        return res;
    }

    /*根据置顶级别列出公告*/
    public List<Object[]> noticeByTop(int start, int length) {
        String HQL = "from TNotice as model1,TAdmin as model2 where model1.adminId= model2.adminId order by model1.noticeTop desc ";
        Session session = getSession();
        List res = session.createQuery(HQL)
                .setFirstResult(start)
                .setMaxResults(length)
                .list();
        session.close();
        return res;
    }

    /*根据商品名称搜索*/
    public List goodSearch(String goodName, int start, int length) {
        String HQL = "from TGood where goodState=:goodState and goodName like '%" + goodName.trim() + "%'";
        Session session = getSession();
        List res = session.createQuery(HQL)
                .setParameter("goodState", Types.good.OnShelves)
                .setFirstResult(start)
                .setMaxResults(length)
                .list();
        session.close();
        return res;
    }

    /*更新库存和销量信息*/
    public int goodInventoryUpdate(int goodId, int count) {
        int res = -1;
        String HQL = "update TGood set goodInventory=goodInventory-:cont,goodSell=goodSell+:cont where goodId=:goodId";
        Session session = getSession();
        Transaction t = session.getTransaction();
        try {
            t.begin();
            res = session
                    .createQuery(HQL)
                    .setParameter("cont", count)
                    .setParameter("goodId", goodId)
                    .executeUpdate();
            t.commit();
        } catch (RuntimeException re) {
            re.printStackTrace();
            t.rollback();
        }
        session.close();
        return res;
    }

    /*恢复库存和销量信息*/
    public int goodInventoryRestore(int goodId, int count) {
        int res = -1;
        String HQL = "update TGood set goodInventory=goodInventory+:cont,goodSell=goodSell-:cont where goodId=:goodId";
        Session session = getSession();
        Transaction t = session.getTransaction();
        try {
            t.begin();
            res = session
                    .createQuery(HQL)
                    .setParameter("cont", count)
                    .setParameter("goodId", goodId)
                    .executeUpdate();
            t.commit();
        } catch (RuntimeException re) {
            re.printStackTrace();
            t.rollback();
        }
        session.close();
        return res;
    }

    /*按日期查找已完成订单*/
    public List financeStatistics(Timestamp from, Timestamp to, int start, int length) {
        String HQL = "from TMyOrder where orderDate >=:frm and orderDate<=:to and orderState=2";
        Session session = getSession();
        List res = session.createQuery(HQL)
                .setParameter("frm", from)
                .setParameter("to", to)
                .setFirstResult(start)
                .setMaxResults(length)
                .list();
        session.close();
        return res;
    }

    /*根据订单ID删除订单和订单商品关系*/
    public int orderDelById(int orderId) {
        int res = -1;
        Session session = getSession();
        Transaction t = session.getTransaction();
        try {
            t.begin();
            String HQL = "delete from TOrderGood where orderId=:orderId";
            session.createQuery(HQL)
                    .setParameter("orderId", orderId)
                    .executeUpdate();
            HQL = "delete from TMyOrder where orderId=:orderId";
            res = session.createQuery(HQL)
                    .setParameter("orderId", orderId)
                    .executeUpdate();
            t.commit();
        } catch (RuntimeException re) {
            re.printStackTrace();
            t.rollback();
        }
        session.close();
        return res;
    }

    public Hib<TAdmin> getAdminDAO() {
        return adminDAO;
    }

    public void setAdminDAO(Hib<TAdmin> adminDAO) {
        this.adminDAO = adminDAO;
    }

    public Hib<TGood> getGoodDAO() {
        return goodDAO;
    }

    public void setGoodDAO(Hib<TGood> goodDAO) {
        this.goodDAO = goodDAO;
    }

    public Hib<TMyOrder> getOrderDAO() {
        return orderDAO;
    }

    public void setOrderDAO(Hib<TMyOrder> orderDAO) {
        this.orderDAO = orderDAO;
    }

    public Hib<TOrderGood> getOrderGoodDAO() {
        return orderGoodDAO;
    }

    public void setOrderGoodDAO(Hib<TOrderGood> orderGoodDAO) {
        this.orderGoodDAO = orderGoodDAO;
    }

    public Hib<TClassification> getClassDAO() {
        return classDAO;
    }

    public void setClassDAO(Hib<TClassification> classDAO) {
        this.classDAO = classDAO;
    }

    public Hib<TNotice> getNoticeDAO() {
        return noticeDAO;
    }

    public void setNoticeDAO(Hib<TNotice> noticeDAO) {
        this.noticeDAO = noticeDAO;
    }

    public Hib<TUser> getUserDAO() {
        return userDAO;
    }

    public void setUserDAO(Hib<TUser> userDAO) {
        this.userDAO = userDAO;
    }

    public Hib<TComment> getCommentDAO() {
        return commentDAO;
    }

    public void setCommentDAO(Hib<TComment> commentDAO) {
        this.commentDAO = commentDAO;
    }

    public Hib<TComment> getTableInfoDAO() {
        return tableInfoDAO;
    }

    public void setTableInfoDAO(Hib<TComment> tableInfoDAO) {
        this.tableInfoDAO = tableInfoDAO;
    }

    public Log getLog() {
        return log;
    }
}
